CREATE TABLE BABEL_5454_T1(COL1_T1 nvarchar(3) NOT NULL, COL2_T1 nvarchar(2) NULL, COL3_T1 decimal (2,1));
GO

CREATE TABLE BABEL_5454_T2(COL1_T2 nvarchar(3) NOT NULL, COL2_T2 numeric(5, 2) NOT NULL, COL3_T2 decimal(9,8));
GO

INSERT INTO BABEL_5454_T1 (COL1_T1, COL2_T1,COL3_T1)
VALUES ('USD', 'US',2.1), ('EUR', 'Eu',1.1), ('GBP', 'Br',3.1), ('JPY', 'Ja',4.1), ('CAD', 'Ca',5.1);
GO

INSERT INTO BABEL_5454_T2 (COL1_T2, COL2_T2, COL3_T2)
VALUES ('EUR', 0.12, 1.12345678), ('GBP', 23.87, 2.12345678), ('JPY', 110.50, 3.12345678), ('CAD', 1.25, 4.12345678), ('USD', 1.00 , 5.12345678);
GO

CREATE TYPE TestUDT FROM decimal(10,4);
GO

CREATE TABLE TestTypes (
    IntCol int,
    FloatCol float,
    SmallIntCol smallint,
    NumericCol numeric(10,5),
    UDTCol TestUDT
);
GO

INSERT INTO TestTypes VALUES
(100, 100.5, 10, 100.12345, 100.1234),
(200, 200.5, 20, 200.12345, 200.1234);
GO

CREATE TABLE BABEL_5454_T3 (id INT IDENTITY(1,1) PRIMARY KEY, in4 DECIMAL(20,6), in5 DECIMAL(20,6));
GO

CREATE TABLE BABEL_5454_T4 (id INT IDENTITY(1,1) PRIMARY KEY, a DECIMAL(20,6));
GO

INSERT INTO BABEL_5454_T3 (in4, in5) VALUES (99999999999999.111111, 11111111111111.999999);
GO

INSERT INTO BABEL_5454_T4 (a) VALUES (99999999999999.111111);
GO

create table BABEL_5454_T7 (a numeric(6,4), b numeric(6,3));
GO

insert into BABEL_5454_T7 values (4, 16);
insert into BABEL_5454_T7 values (10.1234, 10.123);
insert into BABEL_5454_T7 values (1.2, 6);
insert into BABEL_5454_T7 values (NULL, 101.123);
GO


CREATE TABLE BABEL_5454_T8 (id integer PRIMARY KEY, amount decimal(6, 2));
go

INSERT INTO BABEL_5454_T8 VALUES (1, 5000.00);
go

CREATE TABLE BABEL_5454_T9 (id NUMERIC)
GO

INSERT INTO BABEL_5454_T9 VALUES (generate_series(1,100))
GO

CREATE TABLE BABEL_5454_T10 (id NUMERIC(4, 2));
GO
INSERT INTO BABEL_5454_T10 VALUES (1.0)
GO

-- edge cases for scale precision
CREATE TABLE BABEL_5454_T11 (
    COL1_T12 INT,
    COL2_T12 DECIMAL(38,6),
    COL3_T12 DECIMAL(38,0),
    COL4_T12 DECIMAL(10, 7),
    COL5_T12 DECIMAL(4, 0),
    COL6_T12 DECIMAL(30, 5)
);
GO


INSERT INTO BABEL_5454_T11 (COL1_T12, COL2_T12, COL3_T12, COL4_T12, COL5_T12, COL6_T12) VALUES
(1, 99999999999999999999.999999, 99999999999999999999999999, 99.9999, 999, 999999999999999999999.99),
(2, 10000000000000000000.000001, 11111111111111111111111111, 11.1111, 111, 11111111111111111111.11),
(3, 123564.123456, 12345678, 123.4343393, 1234, 1234567890123456789.89),
(4, 1.000000000000001, 1234567890123456789, 0.1234567, 0.5679, 1234567.34)
GO

-- BABEL-5588
CREATE TABLE [BABEL_5454_T12](
    [date_column] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [BABEL_5454_T12] ADD  CONSTRAINT [pk_BABEL_5454_T12] PRIMARY KEY NONCLUSTERED
(
    [date_column] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE TABLE [BABEL_5454_T13](
    [id] [bigint] IDENTITY(39,1) NOT NULL,
    [creation_date] [datetime] NOT NULL,
    [new_biz] [numeric](18, 0) NOT NULL,
    [mid_adj] [numeric](18, 0) NOT NULL,
    [cancels] [numeric](18, 0) NOT NULL,
    [renewals] [numeric](18, 0) NOT NULL,
    [order] [numeric](18, 0) NOT NULL,
    [value] [numeric](18, 0) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [BABEL_5454_T13] ADD  CONSTRAINT [pk_BABEL_5454_T13] PRIMARY KEY NONCLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

INSERT INTO [BABEL_5454_T12] ([date_column])
VALUES
('2023-06-01 00:00:00'),
('2023-06-02 00:00:00');
GO

INSERT INTO [BABEL_5454_T13]
([creation_date],[new_biz], [mid_adj], [cancels], [renewals], [order], [value])
VALUES
('2023-06-01 00:00:00', 1000, 200, 100, 500, 50, 25),
('2023-06-02 00:00:00', 1500, 300, 150, 750, 75, 40);
GO

CREATE VIEW BABEL_5454_V1 AS
SELECT
    ROW_NUMBER() OVER(ORDER BY SUBSTRING(CONVERT(CHAR,CAST(DATEADD(day, 0, theday) AS dateTime), 111),1,10)) AS Row ,
    DATENAME(weekday, DATEADD(day, 0, theday)) AS day,
    volume
FROM (
    SELECT
        D.date_column AS theday,
        SUM([new_biz]+[mid_adj]+[cancels]+[renewals]+[order]+[value]) AS volume
    FROM BABEL_5454_T12 AS D WITH (NOLOCK)
    LEFT JOIN BABEL_5454_T13 AS DBV WITH (NOLOCK)
        ON D.date_column = DBV.creation_date
    WHERE D.date_column >= '2022/12/07'
    GROUP BY D.date_column
) as a
WHERE volume is NOT NULL;
GO

CREATE VIEW BABEL_5454_V2 AS
SELECT
    value2 AS description,
    value1 + COL3_T2 AS sum_num
FROM (
    SELECT
        COL2_T2 AS value1,
        COL2_T1 AS value2,
        COL2_T1,
        COL3_T2
    FROM BABEL_5454_T1
    INNER JOIN BABEL_5454_T2
        ON COL1_T2 = COL1_T1
    UNION ALL
    SELECT
        1 AS aw,
        COL2_T1 AS cr,
        COL2_T1,
        COL3_T1 AS aw1
    FROM BABEL_5454_T1
) a;
GO

CREATE VIEW BABEL_5454_V3 AS
WITH CTE AS (
    SELECT TOP 10 (id+1) AS id
    FROM BABEL_5454_T9
    WHERE id > 50
    ORDER BY id
)
SELECT TOP 1 1 AS constant, id
FROM CTE;
GO

CREATE VIEW BABEL_5454_V4 AS
WITH CTE AS (
    SELECT id + 1.0 AS id_cte
    FROM BABEL_5454_T10
)
SELECT
    CTE.id_cte + 10 AS id_result,
    BABEL_5454_T10.id1 + 10 AS id1_result
FROM CTE
JOIN (
    SELECT TOP 5
        ROW_NUMBER() OVER (ORDER BY id) AS srNo,
        id + 1.0 AS id1
    FROM BABEL_5454_T10
) AS BABEL_5454_T10
ON (1=1);
GO


